package jp.sinya.db;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.*;

import static jp.sinya.Constants.*;

public class DataBaseDemo1 {

    /**
     * jdbc 四大配置参数
     * ①driverClassName: com.mysql.jdbc.Driver
     * ②url: jdbc:mysql://localhost:3306/数据库名
     * ③username: root
     * ④password: qqjy
     * <p>
     * <p>
     * 异常出现的场景：
     * ①ClassNotFoundException: 没导包jar 或者 Class.forName("com.mysql.jdbc.Driver") 写错
     * ②SQLException: getConnection()的三个参数可能有错； 数据库是否开启；
     */
    @Test
    public void test() throws ClassNotFoundException, SQLException {

        //加载驱动类 jdbc4.0以后，不加这句代码也可以
        //Class.forName("com.mysql.jdbc.Driver");

        //连接的数据库
        Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        System.out.println("连接成功");
    }

    @Test
    public void test2() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            statement = connection.createStatement();
            String sql = "select * from student";
            resultSet = statement.executeQuery(sql);

            getColumn(resultSet);
            //getAllRows(resultSet);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Test
    public void test3() {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            statement = connection.createStatement();

            //因为攻击语句的执行，很可能数据库不存在的值，也会得到true
            boolean result1 = attackSQL(statement);
            System.out.println(result1);

            //只有必须满足数据库中能查找到正确的值，才会返回true
            boolean result2 = saveSQL(connection, "Tom", "16");
            System.out.println(result2);

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }

            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }

    public boolean attackSQL(Statement statement) throws SQLException {
        //由于这个sql语句利用or的语法形式，制造一种sql攻击，这个时候正常的查询的话 是返回true的
        String sql = "select * from student where name = 'a' or 'a' = 'a'";
        ResultSet resultSet = statement.executeQuery(sql);
        return resultSet.next();
    }

    /**
     * 给出模板的sql语句，关键的参数用？代替，提高安全性
     *
     * @param connection
     * @param name
     * @param age
     * @return
     * @throws SQLException
     */
    public boolean saveSQL(Connection connection, String name, String age) throws SQLException {
        //由于这个sql语句利用or的语法形式，制造一种sql攻击，这个时候正常的查询的话 是返回true的
        String sql = "select * from student where name = ? and age = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, age);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet.next();
    }

    public void getAllRows(ResultSet resultSet) throws SQLException {
        while (resultSet.next()) {
            //resultSet光标是 行和列，都是从1开始计算
            String id = resultSet.getString(1);
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            String age = resultSet.getString(4);
            String position = resultSet.getString(5);

            System.out.println("ID:" + id + "; Name:" + name + "; sex:" + sex + "; age:" + age + "; position:" + position);
        }
    }

    public void getColumn(ResultSet resultSet) throws SQLException {
        int columnCount = resultSet.getMetaData().getColumnCount();
        System.out.println("columnCount: " + columnCount);
        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                if (i != columnCount) {
                    System.out.print(resultSet.getObject(i) + " ");

                } else {
                    System.out.println(resultSet.getObject(i) + ";");
                }
            }
        }
    }

    /**
     * 加载驱动类的方式获得数据库的connection
     *
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    @Test
    public void test4() throws SQLException, IOException, ClassNotFoundException {
        Connection connection = JdbcUtils.getConnection();
        System.out.println("连接成功...");
    }

    /**
     * 对事务的操作，如果成功会确认commit，如果中间失败了，会回滚rollback
     */
    @Test
    public void test5() {
        Connection connection = null;
        try {
            connection = JdbcUtils.getConnection();
            connection.setAutoCommit(false);//关闭自动提交，相当于开启事务


            String sql = "update student set sex='f' where name=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, "sinya");
            statement.executeUpdate();

            //这里手动制造一个错误，会导致程序无法继续，这个时候回滚操作就会生效
            if (true) {
                throw new RuntimeException("取消更改操作");
            }

            //如果没有报错异常 表示操作成功，这个时候才最终提交事务
            connection.commit();

        } catch (SQLException e) {
            System.out.println(e.toString());

            //发现有错误 回滚取消这一次的操作
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e2) {

            }
        }
    }

    /**
     * dbcp连接池
     */
    @Test
    public void test6() throws SQLException {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/myjava_db");
        dataSource.setUsername("root");
        dataSource.setPassword("qqjy");

        dataSource.setMaxActive(20);
        dataSource.setMinIdle(3);
        dataSource.setMaxWait(1000);

        Connection connection = dataSource.getConnection();
        System.out.println("连接成功...");
        connection.close();
    }

    /**
     * c3p0连接池
     */
    @Test
    public void test7() throws PropertyVetoException, SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/myjava_db");
        dataSource.setUser("root");
        dataSource.setPassword("qqjy");

        dataSource.setAcquireIncrement(5);//增量
        dataSource.setInitialPoolSize(20);//初始化
        dataSource.setMinPoolSize(2);
        dataSource.setMaxPoolSize(50);

        Connection connection = dataSource.getConnection();
        System.out.println("连接成功...");
        connection.close();
    }

    /**
     * 使用c3p0的配置文件来配置连接池
     * 它会默认的去找 c3p0-config.xml文件
     */
    @Test
    public void test8() throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        Connection connection = dataSource.getConnection();
        System.out.println("连接成功...");
        connection.close();
    }

    /**
     * 使用c3p0的配置文件来配置连接池
     * 它会默认的去找 c3p0-config.xml文件
     * 并且通过设置的 名称 去配置文件中找到对应的命名
     */
    @Test
    public void test9() throws SQLException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource("qqjy-config");
        Connection connection = dataSource.getConnection();
        System.out.println("连接成功...");
        connection.close();
    }

    @Test
    public void test10() throws SQLException {
        Student student = new Student("May", "f", 21);
        addStudent(student);
    }

    public void addStudent(Student student) {
        //只需要创建对象时候给出连接池
        QR qr = new QR(JdbcUtils2.getDataSource());
        //student 数据库表中的字段是：id name sex age role
        String sql = "insert into student(name, sex, age) values(?,?,?)";
        Object[] params = {student.getName(), student.getSex(), student.getAge()};
        qr.update(sql, params);
    }

    @Test
    public void test11() throws SQLException {
        //queryStudent("Tom");
        queryStudent2("May");
    }

    public void queryStudent(String name) {
        //只需要创建对象时候给出连接池
        QR qr = new QR(JdbcUtils2.getDataSource());
        //student 数据库表中的字段是：id name sex age role
        String sql = "select * from student where name = ?";
        Object[] params = {name};

        RsHandler handler = resultSet -> {

            try {
                if (!resultSet.next()) {
                    System.out.println("null...");
                    return null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            Student student = null;
            try {
                student = new Student(resultSet.getString("name"), resultSet.getString("sex"), resultSet.getInt("age"));
            } catch (SQLException e) {
                e.printStackTrace();
            }

            System.out.println("student: " + student.getName() + "-" + student.getAge());

            return student;
        };

        qr.query(sql, handler, params);
    }

    public void queryStudent2(String name) throws SQLException {
        //使用commonutils包中的 dbutils 提供的类处理
        QueryRunner qr = new QueryRunner(JdbcUtils2.getDataSource());
        //student 数据库表中的字段是：id name sex age role
        String sql = "select * from student where name = ?";
        Object[] params = {name};
        Student student = qr.query(sql, new BeanHandler<>(Student.class), params);
        System.out.println("student: " + student.getName() + "-" + student.getAge());
    }


}
